﻿using FinancialReportPowerPointAddIn.Helper;
using FinancialReportPowerPointAddIn.PPTModule;
using Microsoft.Office.Interop.PowerPoint;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using LicenseContext = OfficeOpenXml.LicenseContext;

namespace FinancialReportPowerPointAddIn
{
    public partial class GenerateReportForm : Form
    {

        ExcelPackage InputExcel;
        string[] InputFiles;
        int inputYear, inputMonth;
        public GenerateReportForm()
        {
            InitializeComponent();
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            int nowYear = DateTime.Now.Year;
            for (int i = 2019; i <= nowYear; i++)
            {
                cob导入年份.Items.Add(i.ToString());
            }
        }

        private static GenerateReportForm gr = null;

        private void cobSelectType_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cobSelectType.SelectedItem.ToString() == "P&L(Actual)" || cobSelectType.SelectedItem.ToString() == "P&L(Plan)"|| cobSelectType.SelectedItem.ToString()=="资产负债表"||cobSelectType.SelectedItem.ToString() =="利润表")
            {
                cob导入年份.Enabled = true;
                cob导入月份.Enabled = true;
            }
            else
            {
                cob导入年份.Enabled = false;
                cob导入月份.Enabled = false;
            }
        }

        private void btn导入_Click(object sender, EventArgs e)
        {
            if (lab加载.Text != "就绪")
            {
                MessageBox.Show("你没有加载需要导入数据的表格,导入失败!");
                return;
            }
            if (cobSelectType.SelectedIndex == -1)
            {
                MessageBox.Show("你没有选择对应的导入类型,导入失败!");
                return;
            }
            if (cobSelectType.SelectedIndex == 0 || cobSelectType.SelectedIndex == 1)
            {
                if (cob导入年份.SelectedIndex == -1)
                {
                    MessageBox.Show("你没有选择导入对应的年份,导入失败!");
                    return;
                }
                if (cob导入月份.SelectedIndex == -1)
                {
                    MessageBox.Show("你没有选择导入对应的月份,导入失败!");
                    return;
                }
            }
            switch (cobSelectType.SelectedItem.ToString())
            {
                case "P&L(Actual)":
                    InputForPLActual();
                    break;
                case "P&L(Plan)":
                    InputForPLPlan();
                    break;
                case "FOB":
                    InputForFOB();
                    break;
                case "平台Sales":
                    InputForPlatformSales();
                    break;
                case "DayPartSales":
                    InputForDayPart();
                    break;
                case "人员数量":
                    InputManageNumber();
                    break;
                case "CrewLabor":
                    InputCrewLabor();
                    break;
                case "MDS单量":
                    InputMDSQuantity();
                    break;
                case "资产负债表":
                    InputBalanceSheet();
                    break;
                case "利润表":
                    InputProfitSheet();
                    break;
            }
        }

        /// <summary>
        /// 导入资产负债表
        /// </summary>
        private void InputBalanceSheet()
        {
            using (var db = new FinancialReportContext())
            {
                inputYear = Convert.ToInt32(cob导入年份.SelectedItem.ToString());
                inputMonth = Convert.ToInt32(cob导入月份.SelectedItem.ToString());
                foreach (var file in InputFiles)
                {
                    InputExcel = new ExcelPackage(file);
                    BalanceSheetPosition positionRow = new BalanceSheetPosition();
                    bool 是否首次 = true;
                    //循环集合(获取目标数据)       
                    foreach (var childsheet in InputExcel.Workbook.Worksheets)
                    {
                        
                        int 资产期末列 = 4;
                        int 负债期末列 = 8;
                        var pis = positionRow.GetType().GetProperties();
                        string restaurantName = string.Empty;
                        switch (childsheet.Name)
                        {
                            case "潭江店":
                                restaurantName = "潭江";
                                break;
                            case "恩平店":
                                restaurantName = "恩平";
                                break;
                            case "东湖店":
                                restaurantName = "东湖";
                                break;
                            case "西粤店":
                                restaurantName = "西粤";
                                break;
                            case "油十店":
                                restaurantName = "油十";
                                break;
                            case "开大店":
                                restaurantName = "开平大道";
                                break;
                            case "白石店":
                                restaurantName = "白石";
                                break;
                            case "汇总表":
                                restaurantName = "总表";
                                break;
                        }
                        //首次获取行号
                        if (是否首次 == true)
                        {
                            foreach (var prop in pis)
                            {
                                var destCell = childsheet.Cells.Where(x => x.Value?.ToString()?.Contains(prop.Name) == true).FirstOrDefault();
                                positionRow.GetType().GetProperty(prop.Name)?.SetValue(positionRow, destCell?.Start.Row);
                            }
                            是否首次 = false;
                        }
                        if (childsheet.Cells[positionRow.货币资金, 资产期末列].GetCellValue<decimal>() == 0)
                        {
                            MessageBox.Show($"操作{Path.GetFileNameWithoutExtension(file)}文件时存在空数据,导入失败");
                            return;
                        }
                        //判断是不是修改
                        var existItem = db.BalanceSheets.FirstOrDefault(x => x.Restaurant == restaurantName && x.Month == inputMonth && x.Year == inputYear);
                        if (existItem != null)
                        {
                            existItem.货币资金 = childsheet.Cells[positionRow.货币资金, 资产期末列].GetCellValue<decimal>();
                            existItem.短期投资 = childsheet.Cells[positionRow.短期投资, 资产期末列].GetCellValue<decimal>();
                            existItem.公司内部往来 = childsheet.Cells[positionRow.公司内部往来, 资产期末列].GetCellValue<decimal>();
                            existItem.应收账款 = childsheet.Cells[positionRow.应收账款, 资产期末列].GetCellValue<decimal>();
                            existItem.预付款项 = childsheet.Cells[positionRow.预付款项, 资产期末列].GetCellValue<decimal>();
                            existItem.其他应收款 = childsheet.Cells[positionRow.其他应收款, 资产期末列].GetCellValue<decimal>();
                            existItem.存货 = childsheet.Cells[positionRow.存货, 资产期末列].GetCellValue<decimal>();
                            existItem.固定资产账面价值 = childsheet.Cells[positionRow.固定资产账面价值, 资产期末列].GetCellValue<decimal>();
                            existItem.无形资产 = childsheet.Cells[positionRow.无形资产, 资产期末列].GetCellValue<decimal>();
                            existItem.长期待摊费用 = childsheet.Cells[positionRow.长期待摊费用, 资产期末列].GetCellValue<decimal>();
                            existItem.应付账款 = childsheet.Cells[positionRow.应付账款, 负债期末列].GetCellValue<decimal>();
                            existItem.应付职工薪酬 = childsheet.Cells[positionRow.应付职工薪酬, 负债期末列].GetCellValue<decimal>();
                            existItem.应交税费 = childsheet.Cells[positionRow.应交税费, 负债期末列].GetCellValue<decimal>();
                            existItem.其他应付款 = childsheet.Cells[positionRow.其他应付款, 负债期末列].GetCellValue<decimal>();
                            existItem.长期借款 = childsheet.Cells[positionRow.长期借款, 负债期末列].GetCellValue<decimal>();
                            existItem.实收资本 = childsheet.Cells[positionRow.实收资本, 负债期末列].GetCellValue<decimal>();
                            existItem.未分配利润 = childsheet.Cells[positionRow.未分配利润, 负债期末列].GetCellValue<decimal>();
                        }
                        else
                        {
                            //先装入实际数据
                            db.BalanceSheets?.Add(new BalanceSheet()
                            {
                                Restaurant = restaurantName,
                                Month = inputMonth,
                                Year = inputYear,
                                货币资金 = childsheet.Cells[positionRow.货币资金, 资产期末列].GetCellValue<decimal>(),
                                短期投资 = childsheet.Cells[positionRow.短期投资, 资产期末列].GetCellValue<decimal>(),
                                公司内部往来 = childsheet.Cells[positionRow.公司内部往来, 资产期末列].GetCellValue<decimal>(),
                                应收账款 = childsheet.Cells[positionRow.应收账款, 资产期末列].GetCellValue<decimal>(),
                                预付款项 = childsheet.Cells[positionRow.预付款项, 资产期末列].GetCellValue<decimal>(),
                                其他应收款 = childsheet.Cells[positionRow.其他应收款, 资产期末列].GetCellValue<decimal>(),
                                存货 = childsheet.Cells[positionRow.存货, 资产期末列].GetCellValue<decimal>(),
                                固定资产账面价值 = childsheet.Cells[positionRow.固定资产账面价值, 资产期末列].GetCellValue<decimal>(),
                                无形资产 = childsheet.Cells[positionRow.无形资产, 资产期末列].GetCellValue<decimal>(),
                                长期待摊费用 = childsheet.Cells[positionRow.长期待摊费用, 资产期末列].GetCellValue<decimal>(),
                                应付账款 = childsheet.Cells[positionRow.应付账款, 负债期末列].GetCellValue<decimal>(),
                                应付职工薪酬 = childsheet.Cells[positionRow.应付职工薪酬, 负债期末列].GetCellValue<decimal>(),
                                应交税费 = childsheet.Cells[positionRow.应交税费, 负债期末列].GetCellValue<decimal>(),
                                其他应付款 = childsheet.Cells[positionRow.其他应付款, 负债期末列].GetCellValue<decimal>(),
                                长期借款 = childsheet.Cells[positionRow.长期借款, 负债期末列].GetCellValue<decimal>(),
                                实收资本 = childsheet.Cells[positionRow.实收资本, 负债期末列].GetCellValue<decimal>(),
                                未分配利润 = childsheet.Cells[positionRow.未分配利润, 负债期末列].GetCellValue<decimal>()
                            });
                        }
                    }
                    int count = db.SaveChanges();
                    if (count > 0)
                    {
                        MessageBox.Show($"操作{Path.GetFileNameWithoutExtension(file)}文件导入资产负债表成功!");
                    }
                    else
                    {
                        MessageBox.Show($"操作{Path.GetFileNameWithoutExtension(file)}文件导入资产负债表失败!");
                    }
                }
            }
        }

        /// <summary>
        /// 导入利润表
        /// </summary>
        private void InputProfitSheet()
        {
            using (var db = new FinancialReportContext())
            {
                inputYear = Convert.ToInt32(cob导入年份.SelectedItem.ToString());
                inputMonth = Convert.ToInt32(cob导入月份.SelectedItem.ToString());
                foreach (var file in InputFiles)
                {
                    InputExcel = new ExcelPackage(file);
                    ProfitPosition positionRow = new ProfitPosition();
                    bool 是否首次 = true;
                    //循环集合(获取目标数据)       
                    foreach (var childsheet in InputExcel.Workbook.Worksheets)
                    {
                        var xx = childsheet.Cells.Where(x => x.Value?.Equals("本月金额") == true).FirstOrDefault();
                        if (xx == null)
                        {
                            MessageBox.Show($"操作{Path.GetFileNameWithoutExtension(file)}文件时无法获取单元格,导入失败");
                            return;
                        }
                        int 本月列 = xx.Start.Column;
                        var pis = positionRow.GetType().GetProperties();
                        string restaurantName = string.Empty;
                        switch (childsheet.Name)
                        {
                            case "潭江店":
                                restaurantName = "潭江";
                                break;
                            case "恩平店":
                                restaurantName = "恩平";
                                break;
                            case "东湖店":
                                restaurantName = "东湖";
                                break;
                            case "西粤店":
                                restaurantName = "西粤";
                                break;
                            case "油十店":
                                restaurantName = "油十";
                                break;
                            case "开大店":
                                restaurantName = "开平大道";
                                break;
                            case "白石店":
                                restaurantName = "白石";
                                break;
                            case "汇总表":
                                restaurantName = "总表";
                                break;
                        }
                        //首次获取行号
                        if (是否首次 == true)
                        {
                            foreach (var prop in pis)
                            {
                                var destCell = childsheet.Cells.Where(x => x.Value?.ToString()?.Contains(prop.Name) == true).FirstOrDefault();
                                positionRow.GetType().GetProperty(prop.Name)?.SetValue(positionRow, destCell?.Start.Row);
                            }
                            是否首次 = false;
                        }
                        if (childsheet.Cells[positionRow.主营业务收入, 本月列].GetCellValue<decimal>() == 0)
                        {
                            MessageBox.Show($"操作{Path.GetFileNameWithoutExtension(file)}文件时存在空数据,导入失败");
                            return;
                        }
                        //判断是不是修改
                        var existItem = db.ProfitSheets.FirstOrDefault(x => x.Restaurant == restaurantName && x.Month == inputMonth && x.Year == inputYear);
                        if (existItem != null)
                        {
                            existItem.主营业务收入 = childsheet.Cells[positionRow.主营业务收入, 本月列].GetCellValue<decimal>();
                            existItem.主营业务成本 = childsheet.Cells[positionRow.主营业务成本, 本月列].GetCellValue<decimal>();
                            existItem.其他业务收入 = childsheet.Cells[positionRow.其他业务收入, 本月列].GetCellValue<decimal>();
                            existItem.其他业务支出 = childsheet.Cells[positionRow.其他业务支出, 本月列].GetCellValue<decimal>();
                            existItem.销售费用 = childsheet.Cells[positionRow.销售费用, 本月列].GetCellValue<decimal>();
                            existItem.管理费用 = childsheet.Cells[positionRow.管理费用, 本月列].GetCellValue<decimal>();
                            existItem.财务费用 = childsheet.Cells[positionRow.财务费用, 本月列].GetCellValue<decimal>();
                            existItem.营业外收入 = childsheet.Cells[positionRow.营业外收入, 本月列].GetCellValue<decimal>();
                            existItem.营业外支出 = childsheet.Cells[positionRow.营业外支出, 本月列].GetCellValue<decimal>();
                            existItem.所得税费用 = childsheet.Cells[positionRow.所得税费用, 本月列].GetCellValue<decimal>();
                        }
                        else
                        {
                            //先装入实际数据
                            db.ProfitSheets?.Add(new ProfitSheet()
                            {
                                Restaurant = restaurantName,                               
                                Month = inputMonth,
                                Year = inputYear,
                                主营业务收入 = childsheet.Cells[positionRow.主营业务收入, 本月列].GetCellValue<decimal>(),
                                主营业务成本 = childsheet.Cells[positionRow.主营业务成本, 本月列].GetCellValue<decimal>(),
                                其他业务收入 = childsheet.Cells[positionRow.其他业务收入, 本月列].GetCellValue<decimal>(),
                                其他业务支出 = childsheet.Cells[positionRow.其他业务支出, 本月列].GetCellValue<decimal>(),
                                销售费用 = childsheet.Cells[positionRow.销售费用, 本月列].GetCellValue<decimal>(),
                                管理费用 = childsheet.Cells[positionRow.管理费用, 本月列].GetCellValue<decimal>(),
                                财务费用 = childsheet.Cells[positionRow.财务费用, 本月列].GetCellValue<decimal>(),
                                营业外收入 = childsheet.Cells[positionRow.营业外收入, 本月列].GetCellValue<decimal>(),
                                营业外支出 = childsheet.Cells[positionRow.营业外支出, 本月列].GetCellValue<decimal>(),
                                所得税费用 = childsheet.Cells[positionRow.所得税费用, 本月列].GetCellValue<decimal>()
                            });
                        }
                    }
                    int count = db.SaveChanges();
                    if (count > 0)
                    {
                        MessageBox.Show($"操作{Path.GetFileNameWithoutExtension(file)}文件导入利润表成功!");
                    }
                    else
                    {
                        MessageBox.Show($"操作{Path.GetFileNameWithoutExtension(file)}文件导入利润表失败!");
                    }
                }
            }
        }

        /// <summary>
        /// 批量导入P&L(Actual)
        /// </summary>
        private void InputForPLActual()
        {
            using (var db = new FinancialReportContext())
            {
                inputYear = Convert.ToInt32(cob导入年份.SelectedItem.ToString());
                inputMonth = Convert.ToInt32(cob导入月份.SelectedItem.ToString());
                foreach (var file in InputFiles)
                {
                    InputExcel = new ExcelPackage(file);
                    PLPosition positionRow = new PLPosition();
                    bool 是否首次 = true;
                    //循环集合(获取目标数据)                    
                    foreach (var childsheet in InputExcel.Workbook.Worksheets)
                    {
                        //先获要导入月份的Sales数据                
                        double date = new DateTime(inputYear, inputMonth, 1).ToOADate();
                        var xx = childsheet.Cells.Where(x => x.Value?.Equals(date) == true).FirstOrDefault();
                        int financialCol = 0;
                        if (xx == null)
                        {
                            MessageBox.Show($"操作{Path.GetFileNameWithoutExtension(file)}文件时无法获取合并单元格,导入失败");
                            return;
                        }
                        if (xx.Merge)
                        {
                            var idx = childsheet.GetMergeCellId(xx.Start.Row, xx.Start.Column);
                            var mcells = childsheet.MergedCells[idx - 1];
                            //获取财务实际所在列
                            ExcelAddress mergeAddress = new ExcelAddress(mcells);
                            for (int j = mergeAddress.Start.Column; j <= mergeAddress.End.Column; j++)
                            {
                                if (childsheet.Cells[mergeAddress.Start.Row + 1, j].Value?.ToString()?.Contains("财务实际") == true)
                                {
                                    financialCol = j;
                                }
                            }
                        }
                        else
                        {
                            MessageBox.Show($"操作{Path.GetFileNameWithoutExtension(file)}文件时定位P&L日期坐标失败,生成失败");
                            return;
                        }
                        var pis = positionRow.GetType().GetProperties();
                        //首次获取行号
                        if (是否首次 == true)
                        {
                            foreach (var prop in pis)
                            {
                                var destCell = childsheet.Cells.Where(x => x.Value?.ToString()?.Contains(prop.Name) == true).FirstOrDefault();
                                positionRow.GetType().GetProperty(prop.Name)?.SetValue(positionRow, destCell?.Start.Row);
                            }
                            是否首次 = false;
                        }
                        if (childsheet.Cells[positionRow.产品净收入, financialCol].GetCellValue<decimal>() == 0)
                        {
                            MessageBox.Show($"操作{Path.GetFileNameWithoutExtension(file)}文件时存在空数据,导入失败");
                            return;
                        }
                        //判断是不是修改
                        var existItem = db.PLDatas.FirstOrDefault(x => x.IsPlan == false && x.Restaurant == childsheet.Name && x.Month == inputMonth && x.Year == inputYear);
                        if (existItem != null)
                        {
                            existItem.产品净收入 = childsheet.Cells[positionRow.产品净收入, financialCol].GetCellValue<decimal>();
                            existItem.食品成本 = childsheet.Cells[positionRow.食品成本, financialCol].GetCellValue<decimal>();
                            existItem.包装成本 = childsheet.Cells[positionRow.包装成本, financialCol].GetCellValue<decimal>();
                            existItem.运费 = childsheet.Cells[positionRow.运费, financialCol].GetCellValue<decimal>();
                            existItem.员工薪资 = childsheet.Cells[positionRow.员工薪资, financialCol].GetCellValue<decimal>();
                            existItem.管理组薪资 = childsheet.Cells[positionRow.管理组薪资, financialCol].GetCellValue<decimal>();
                            existItem.薪资税 = childsheet.Cells[positionRow.薪资税, financialCol].GetCellValue<decimal>();
                            existItem.广告 = childsheet.Cells[positionRow.广告, financialCol].GetCellValue<decimal>();
                            existItem.促销 = childsheet.Cells[positionRow.促销, financialCol].GetCellValue<decimal>();
                            existItem.外部服务 = childsheet.Cells[positionRow.外部服务, financialCol].GetCellValue<decimal>();
                            existItem.制服 = childsheet.Cells[positionRow.制服, financialCol].GetCellValue<decimal>();
                            existItem.营运物料 = childsheet.Cells[positionRow.营运物料, financialCol].GetCellValue<decimal>();
                            existItem.维护保养 = childsheet.Cells[positionRow.维护保养, financialCol].GetCellValue<decimal>();
                            existItem.公共事业 = childsheet.Cells[positionRow.公共事业, financialCol].GetCellValue<decimal>();
                            existItem.办公用品 = childsheet.Cells[positionRow.办公用品, financialCol].GetCellValue<decimal>();
                            existItem.现金分差 = childsheet.Cells[positionRow.现金分差, financialCol].GetCellValue<decimal>();
                            existItem.杂费 = childsheet.Cells[positionRow.杂费, financialCol].GetCellValue<decimal>();
                            existItem.租金 = childsheet.Cells[positionRow.租金, financialCol].GetCellValue<decimal>();
                            existItem.服务费 = childsheet.Cells[positionRow.服务费, financialCol].GetCellValue<decimal>();
                            existItem.法律费用 = childsheet.Cells[positionRow.法律费用, financialCol].GetCellValue<decimal>();
                            existItem.保险 = childsheet.Cells[positionRow.保险, financialCol].GetCellValue<decimal>();
                            existItem.折旧 = childsheet.Cells[positionRow.折旧, financialCol].GetCellValue<decimal>();
                            existItem.其他费用 = childsheet.Cells[positionRow.其他费用, financialCol].GetCellValue<decimal>();
                            existItem.非产品净收入 = childsheet.Cells[positionRow.非产品净收入, financialCol].GetCellValue<decimal>();
                            existItem.非产品成本 = childsheet.Cells[positionRow.非产品成本, financialCol].GetCellValue<decimal>();
                            existItem.特许经营行政费用 = childsheet.Cells[positionRow.特许经营行政费用, financialCol].GetCellValue<decimal>();
                            existItem.再投资 = childsheet.Cells[positionRow.再投资, financialCol].GetCellValue<decimal>();
                            existItem.利息 = childsheet.Cells[positionRow.利息, financialCol].GetCellValue<decimal>();
                            existItem.本金 = childsheet.Cells[positionRow.本金, financialCol].GetCellValue<decimal>();
                        }
                        else
                        {
                            //先装入实际数据
                            db.PLDatas?.Add(new PLData()
                            {
                                Restaurant = childsheet.Name,
                                IsPlan = false,
                                Month = inputMonth,
                                Year = inputYear,
                                产品净收入 = childsheet.Cells[positionRow.产品净收入, financialCol].GetCellValue<decimal>(),
                                食品成本 = childsheet.Cells[positionRow.食品成本, financialCol].GetCellValue<decimal>(),
                                包装成本 = childsheet.Cells[positionRow.包装成本, financialCol].GetCellValue<decimal>(),
                                运费 = childsheet.Cells[positionRow.运费, financialCol].GetCellValue<decimal>(),
                                员工薪资 = childsheet.Cells[positionRow.员工薪资, financialCol].GetCellValue<decimal>(),
                                管理组薪资 = childsheet.Cells[positionRow.管理组薪资, financialCol].GetCellValue<decimal>(),
                                薪资税 = childsheet.Cells[positionRow.薪资税, financialCol].GetCellValue<decimal>(),
                                广告 = childsheet.Cells[positionRow.广告, financialCol].GetCellValue<decimal>(),
                                促销 = childsheet.Cells[positionRow.促销, financialCol].GetCellValue<decimal>(),
                                外部服务 = childsheet.Cells[positionRow.外部服务, financialCol].GetCellValue<decimal>(),
                                制服 = childsheet.Cells[positionRow.制服, financialCol].GetCellValue<decimal>(),
                                营运物料 = childsheet.Cells[positionRow.营运物料, financialCol].GetCellValue<decimal>(),
                                维护保养 = childsheet.Cells[positionRow.维护保养, financialCol].GetCellValue<decimal>(),
                                公共事业 = childsheet.Cells[positionRow.公共事业, financialCol].GetCellValue<decimal>(),
                                办公用品 = childsheet.Cells[positionRow.办公用品, financialCol].GetCellValue<decimal>(),
                                现金分差 = childsheet.Cells[positionRow.现金分差, financialCol].GetCellValue<decimal>(),
                                杂费 = childsheet.Cells[positionRow.杂费, financialCol].GetCellValue<decimal>(),
                                租金 = childsheet.Cells[positionRow.租金, financialCol].GetCellValue<decimal>(),
                                服务费 = childsheet.Cells[positionRow.服务费, financialCol].GetCellValue<decimal>(),
                                法律费用 = childsheet.Cells[positionRow.法律费用, financialCol].GetCellValue<decimal>(),
                                保险 = childsheet.Cells[positionRow.保险, financialCol].GetCellValue<decimal>(),
                                折旧 = childsheet.Cells[positionRow.折旧, financialCol].GetCellValue<decimal>(),
                                其他费用 = childsheet.Cells[positionRow.其他费用, financialCol].GetCellValue<decimal>(),
                                非产品净收入 = childsheet.Cells[positionRow.非产品净收入, financialCol].GetCellValue<decimal>(),
                                非产品成本 = childsheet.Cells[positionRow.非产品成本, financialCol].GetCellValue<decimal>(),
                                特许经营行政费用 = childsheet.Cells[positionRow.特许经营行政费用, financialCol].GetCellValue<decimal>(),
                                再投资 = childsheet.Cells[positionRow.再投资, financialCol].GetCellValue<decimal>(),
                                利息 = childsheet.Cells[positionRow.利息, financialCol].GetCellValue<decimal>(),
                                本金 = childsheet.Cells[positionRow.本金, financialCol].GetCellValue<decimal>(),
                            });
                        }
                    }
                    int count = db.SaveChanges();
                    if (count > 0)
                    {
                        MessageBox.Show($"操作{Path.GetFileNameWithoutExtension(file)}文件导入P&L成功!");
                    }
                    else
                    {
                        MessageBox.Show($"操作{Path.GetFileNameWithoutExtension(file)}文件导入P&L失败!");
                    }
                }
            }
        }

        /// <summary>
        /// 批量导入P&L(Plan)
        /// </summary>
        private void InputForPLPlan()
        {
            using (var db = new FinancialReportContext())
            {
                inputYear = Convert.ToInt32(cob导入年份.SelectedItem.ToString());
                inputMonth = Convert.ToInt32(cob导入月份.SelectedItem.ToString());
                foreach (var file in InputFiles)
                {
                    InputExcel = new ExcelPackage(file);
                    PLPosition positionRow = new PLPosition();
                    bool 是否首次 = true;
                    //循环集合(获取目标数据)                    
                    foreach (var childsheet in InputExcel.Workbook.Worksheets)
                    {
                        //先获要导入月份的Sales数据                
                        double date = new DateTime(inputYear, inputMonth, 1).ToOADate();
                        var xx = childsheet.Cells.Where(x => x.Value?.Equals(date) == true).FirstOrDefault();
                        int planCol = 0;
                        if (xx == null)
                        {
                            MessageBox.Show($"操作{Path.GetFileNameWithoutExtension(file)}文件时无法获取合并单元格,导入失败");
                            return;
                        }
                        if (xx.Merge)
                        {
                            var idx = childsheet.GetMergeCellId(xx.Start.Row, xx.Start.Column);
                            var mcells = childsheet.MergedCells[idx - 1];
                            //获取财务实际所在列
                            ExcelAddress mergeAddress = new ExcelAddress(mcells);
                            for (int j = mergeAddress.Start.Column; j <= mergeAddress.End.Column; j++)
                            {
                                if (childsheet.Cells[mergeAddress.Start.Row + 1, j].Value?.ToString()?.Contains("Plan") == true)
                                {
                                    planCol = j;
                                }
                            }
                        }
                        else
                        {
                            MessageBox.Show($"操作{Path.GetFileNameWithoutExtension(file)}文件时定位P&L日期坐标失败,生成失败");
                            return;
                        }
                        var pis = positionRow.GetType().GetProperties();
                        //首次获取行号
                        if (是否首次 == true)
                        {
                            foreach (var prop in pis)
                            {
                                var destCell = childsheet.Cells.Where(x => x.Value?.ToString()?.Contains(prop.Name) == true).FirstOrDefault();
                                positionRow.GetType().GetProperty(prop.Name)?.SetValue(positionRow, destCell?.Start.Row);
                            }
                            是否首次 = false;
                        }
                        if (childsheet.Cells[positionRow.产品净收入, planCol].GetCellValue<decimal>() == 0)
                        {
                            MessageBox.Show($"操作{Path.GetFileNameWithoutExtension(file)}文件时存在空数据,导入失败");
                            return;
                        }
                        //判断是不是修改
                        var existItem = db.PLDatas.FirstOrDefault(x => x.IsPlan == true && x.Restaurant == childsheet.Name && x.Month == inputMonth && x.Year == inputYear);
                        if (existItem != null)
                        {
                            existItem.产品净收入 = childsheet.Cells[positionRow.产品净收入, planCol].GetCellValue<decimal>();
                            existItem.食品成本 = childsheet.Cells[positionRow.食品成本, planCol].GetCellValue<decimal>();
                            existItem.包装成本 = childsheet.Cells[positionRow.包装成本, planCol].GetCellValue<decimal>();
                            existItem.运费 = childsheet.Cells[positionRow.运费, planCol].GetCellValue<decimal>();
                            existItem.员工薪资 = childsheet.Cells[positionRow.员工薪资, planCol].GetCellValue<decimal>();
                            existItem.管理组薪资 = childsheet.Cells[positionRow.管理组薪资, planCol].GetCellValue<decimal>();
                            existItem.薪资税 = childsheet.Cells[positionRow.薪资税, planCol].GetCellValue<decimal>();
                            existItem.广告 = childsheet.Cells[positionRow.广告, planCol].GetCellValue<decimal>();
                            existItem.促销 = childsheet.Cells[positionRow.促销, planCol].GetCellValue<decimal>();
                            existItem.外部服务 = childsheet.Cells[positionRow.外部服务, planCol].GetCellValue<decimal>();
                            existItem.制服 = childsheet.Cells[positionRow.制服, planCol].GetCellValue<decimal>();
                            existItem.营运物料 = childsheet.Cells[positionRow.营运物料, planCol].GetCellValue<decimal>();
                            existItem.维护保养 = childsheet.Cells[positionRow.维护保养, planCol].GetCellValue<decimal>();
                            existItem.公共事业 = childsheet.Cells[positionRow.公共事业, planCol].GetCellValue<decimal>();
                            existItem.办公用品 = childsheet.Cells[positionRow.办公用品, planCol].GetCellValue<decimal>();
                            existItem.现金分差 = childsheet.Cells[positionRow.现金分差, planCol].GetCellValue<decimal>();
                            existItem.杂费 = childsheet.Cells[positionRow.杂费, planCol].GetCellValue<decimal>();
                            existItem.租金 = childsheet.Cells[positionRow.租金, planCol].GetCellValue<decimal>();
                            existItem.服务费 = childsheet.Cells[positionRow.服务费, planCol].GetCellValue<decimal>();
                            existItem.法律费用 = childsheet.Cells[positionRow.法律费用, planCol].GetCellValue<decimal>();
                            existItem.保险 = childsheet.Cells[positionRow.保险, planCol].GetCellValue<decimal>();
                            existItem.折旧 = childsheet.Cells[positionRow.折旧, planCol].GetCellValue<decimal>();
                            existItem.其他费用 = childsheet.Cells[positionRow.其他费用, planCol].GetCellValue<decimal>();
                            existItem.非产品净收入 = childsheet.Cells[positionRow.非产品净收入, planCol].GetCellValue<decimal>();
                            existItem.非产品成本 = childsheet.Cells[positionRow.非产品成本, planCol].GetCellValue<decimal>();
                            existItem.特许经营行政费用 = childsheet.Cells[positionRow.特许经营行政费用, planCol].GetCellValue<decimal>();
                            existItem.再投资 = childsheet.Cells[positionRow.再投资, planCol].GetCellValue<decimal>();
                            existItem.利息 = childsheet.Cells[positionRow.利息, planCol].GetCellValue<decimal>();
                            existItem.本金 = childsheet.Cells[positionRow.本金, planCol].GetCellValue<decimal>();
                        }
                        else
                        {
                            //再装入Plan数据
                            db.PLDatas?.Add(new PLData()
                            {
                                Restaurant = childsheet.Name,
                                IsPlan = true,
                                Month = inputMonth,
                                Year = inputYear,
                                产品净收入 = childsheet.Cells[positionRow.产品净收入, planCol].GetCellValue<decimal>(),
                                食品成本 = childsheet.Cells[positionRow.食品成本, planCol].GetCellValue<decimal>(),
                                包装成本 = childsheet.Cells[positionRow.包装成本, planCol].GetCellValue<decimal>(),
                                运费 = childsheet.Cells[positionRow.运费, planCol].GetCellValue<decimal>(),
                                员工薪资 = childsheet.Cells[positionRow.员工薪资, planCol].GetCellValue<decimal>(),
                                管理组薪资 = childsheet.Cells[positionRow.管理组薪资, planCol].GetCellValue<decimal>(),
                                薪资税 = childsheet.Cells[positionRow.薪资税, planCol].GetCellValue<decimal>(),
                                广告 = childsheet.Cells[positionRow.广告, planCol].GetCellValue<decimal>(),
                                促销 = childsheet.Cells[positionRow.促销, planCol].GetCellValue<decimal>(),
                                外部服务 = childsheet.Cells[positionRow.外部服务, planCol].GetCellValue<decimal>(),
                                制服 = childsheet.Cells[positionRow.制服, planCol].GetCellValue<decimal>(),
                                营运物料 = childsheet.Cells[positionRow.营运物料, planCol].GetCellValue<decimal>(),
                                维护保养 = childsheet.Cells[positionRow.维护保养, planCol].GetCellValue<decimal>(),
                                公共事业 = childsheet.Cells[positionRow.公共事业, planCol].GetCellValue<decimal>(),
                                办公用品 = childsheet.Cells[positionRow.办公用品, planCol].GetCellValue<decimal>(),
                                现金分差 = childsheet.Cells[positionRow.现金分差, planCol].GetCellValue<decimal>(),
                                杂费 = childsheet.Cells[positionRow.杂费, planCol].GetCellValue<decimal>(),
                                租金 = childsheet.Cells[positionRow.租金, planCol].GetCellValue<decimal>(),
                                服务费 = childsheet.Cells[positionRow.服务费, planCol].GetCellValue<decimal>(),
                                法律费用 = childsheet.Cells[positionRow.法律费用, planCol].GetCellValue<decimal>(),
                                保险 = childsheet.Cells[positionRow.保险, planCol].GetCellValue<decimal>(),
                                折旧 = childsheet.Cells[positionRow.折旧, planCol].GetCellValue<decimal>(),
                                其他费用 = childsheet.Cells[positionRow.其他费用, planCol].GetCellValue<decimal>(),
                                非产品净收入 = childsheet.Cells[positionRow.非产品净收入, planCol].GetCellValue<decimal>(),
                                非产品成本 = childsheet.Cells[positionRow.非产品成本, planCol].GetCellValue<decimal>(),
                                特许经营行政费用 = childsheet.Cells[positionRow.特许经营行政费用, planCol].GetCellValue<decimal>(),
                                再投资 = childsheet.Cells[positionRow.再投资, planCol].GetCellValue<decimal>(),
                                利息 = childsheet.Cells[positionRow.利息, planCol].GetCellValue<decimal>(),
                                本金 = childsheet.Cells[positionRow.本金, planCol].GetCellValue<decimal>(),
                            });
                        }
                    }
                    int count = db.SaveChanges();
                    if (count > 0)
                    {
                        MessageBox.Show($"操作{Path.GetFileNameWithoutExtension(file)}文件导入P&L成功!");
                    }
                    else
                    {
                        MessageBox.Show($"操作{Path.GetFileNameWithoutExtension(file)}文件导入P&L失败!");
                    }
                }
            }
        }


        /// <summary>
        /// 批量导入FOB
        /// </summary>
        private void InputForFOB()
        {
            using (var db = new FinancialReportContext())
            {
                foreach (var file in InputFiles)
                {
                    InputExcel = new ExcelPackage(file);
                    ExcelWorksheet fobsheet = InputExcel.Workbook.Worksheets[0];
                    for (int r = fobsheet.Dimension.Start.Row + 1; r <= fobsheet.Dimension.End.Row; r++)
                    {
                        db.Fobs?.Add(new Fob()
                        {
                            Uscode = fobsheet.Cells[r, 2].Value.ToString(),
                            NetProductSales = fobsheet.Cells[r, 3].GetValue<decimal>(),
                            BaseFoodCost = fobsheet.Cells[r, 4].GetValue<decimal>(),
                            BaseFoodPercentage = fobsheet.Cells[r, 5].GetValue<double>(),
                            EMealCost = fobsheet.Cells[r, 6].GetValue<decimal>(),
                            EMealPercentage = fobsheet.Cells[r, 7].GetValue<double>(),
                            TargetEMealPercentage = fobsheet.Cells[r, 8].GetValue<double>(),
                            CompleteWasteCost = fobsheet.Cells[r, 9].GetValue<decimal>(),
                            CompleteWastePercentage = fobsheet.Cells[r, 10].GetValue<double>(),
                            TargetCompleteWastePercentage = fobsheet.Cells[r, 11].GetValue<double>(),
                            RawWasteCost = fobsheet.Cells[r, 12].GetValue<decimal>(),
                            RawWastePercentage = fobsheet.Cells[r, 13].GetValue<double>(),
                            TargetRawWastePercentage = fobsheet.Cells[r, 14].GetValue<double>(),
                            CommonItemCost = fobsheet.Cells[r, 15].GetValue<decimal>(),
                            CommonItemPercentage = fobsheet.Cells[r, 16].GetValue<double>(),
                            TargetCommonItemPercentage = fobsheet.Cells[r, 17].GetValue<double>(),
                            YieldDiffCost = fobsheet.Cells[r, 18].GetValue<decimal>(),
                            YieldDiffPercentage = fobsheet.Cells[r, 19].GetValue<double>(),
                            TargetYieldDiffPercentage = fobsheet.Cells[r, 20].GetValue<double>(),
                            StatDiffCost = fobsheet.Cells[r, 21].GetValue<decimal>(),
                            StatDiffPercentage = fobsheet.Cells[r, 22].GetValue<double>(),
                            TargetStatDiffPercentage = fobsheet.Cells[r, 23].GetValue<double>(),
                            TotalControlCost = fobsheet.Cells[r, 24].GetValue<decimal>(),
                            TotalControlPercentage = fobsheet.Cells[r, 25].GetValue<double>(),
                            TargetTotalControlPercentage = fobsheet.Cells[r, 26].GetValue<double>(),
                            TotalCost = fobsheet.Cells[r, 27].GetValue<decimal>(),
                            TotalCostPercentage = fobsheet.Cells[r, 28].GetValue<double>(),
                            PromoCost = fobsheet.Cells[r, 29].GetValue<decimal>(),
                            PromoPercentage = fobsheet.Cells[r, 30].GetValue<double>(),
                            ActualCost = fobsheet.Cells[r, 31].GetValue<decimal>(),
                            ActualCostPercentage = fobsheet.Cells[r, 32].GetValue<double>(),
                            OtherAdj = fobsheet.Cells[r, 33].GetValue<decimal>(),
                            OtherAdjPercentage = fobsheet.Cells[r, 34].GetValue<double>(),
                            UnexplainCost = fobsheet.Cells[r, 35].GetValue<decimal>(),
                            UnexplainCostPercentage = fobsheet.Cells[r, 36].GetValue<double>(),
                            ActualFOBPercentage = fobsheet.Cells[r, 37].GetValue<double>(),
                            TargetFOBPercentage = fobsheet.Cells[r, 38].GetValue<double>(),
                            Year = Convert.ToInt32(Path.GetFileNameWithoutExtension(file).Split('-')[0]),
                            Month = Convert.ToInt32(Path.GetFileNameWithoutExtension(file).Split('-')[1])
                        });
                    }
                    int count = db.SaveChanges();
                    if (count > 0)
                    {
                        MessageBox.Show($"导入完成{Path.GetFileNameWithoutExtension(file)}FOB文件，导入了{count}条数据！");
                    }
                    else
                    {
                        MessageBox.Show($"导入失败{Path.GetFileNameWithoutExtension(file)}FOB文件！");
                    }
                }
            }
        }

        private void btn生成_Click(object sender, EventArgs e)
        {
            int currentYear = Convert.ToInt32(cobCurrentYear.SelectedItem.ToString());
            int comYear = Convert.ToInt32(cobCompYear.SelectedItem.ToString());
            int currentMonth = Convert.ToInt32(cobCurrentMonth.SelectedItem.ToString());
            int comMonth = Convert.ToInt32(cobCompMonth.SelectedItem.ToString());
            // 获取当前活动的PPT应用程序
            Microsoft.Office.Interop.PowerPoint.Application pptApp = Globals.ThisAddIn.Application;
            // 获取当前活动的PPT文档
            Presentation pptDoc = pptApp.ActivePresentation;
            // 删除所有幻灯片  
            for (int i = pptDoc.Slides.Count; i >= 1; i--)
            {
                pptDoc.Slides[i].Delete();
            }
            foreach (string item in listBoxRight.Items)
            {
                switch (item)
                {
                    case "MTD总体概况":
                        GenerateMTDProfitDetail.Write(currentYear, currentMonth, comYear, comMonth);
                        break;
                    case "营业额分布差异":
                        GenerateMTDSalesDifferences.Write(currentYear, currentMonth, comYear, comMonth);
                        break;
                    case "P&L重点项目":
                        GenerateHightLightSummary.Write(currentYear, currentMonth, comYear, comMonth);
                        GenerateHightLightSummary2.Write(currentYear, currentMonth, comYear, comMonth);
                        break;
                    case "FOB趋势":
                        GenerateFobTrend.Write(currentYear, currentMonth);
                        break;
                    case "PTHR趋势":
                        GeneratePthrTrend.Write(currentYear, currentMonth);
                        break;
                    case "SPMH趋势":
                        GenerateSpmhTrend.Write(currentYear, currentMonth);
                        break;
                    case "汇总四方图":
                        GenerateFourPartData.Write(currentYear, currentMonth, comYear, comMonth, "汇总");
                        break;
                    case "SalesComp趋势":
                        GenerateSalesTrend.Write(currentYear, currentMonth);
                        break;
                    case "整体现金流差异":
                        GenerateCashFlowDiff.Write(currentYear, currentMonth, comYear, comMonth);
                        break;
                    case "YTD整体现金流差异":
                        GenerateYTDCashFlowDiff.Write(currentYear, currentMonth, comYear, comMonth);
                        break;
                    case "单店分析":
                        string[] restaurants = new string[] { "东湖店", "恩平店", "潭江店", "白石店", "西粤店", "油十店", "开大店" };
                        foreach (string target in restaurants)
                        {
                            GenerateFourPartData.Write(currentYear, currentMonth, comYear, comMonth, target);
                            GenerateSinglePlatformSalesTrend.Write(currentYear, currentMonth, target);
                            GenerateSingleControlProfitDiff.Write(currentYear, currentMonth, comYear, comMonth, target);
                        }
                        break;
                    case "财务概况":
                        GenerateFinancialOverview.Write(currentYear, currentMonth, comYear, comMonth);
                        break;
                    case "MDS趋势":
                        GeneratePlatformMdsSalesTrend.Write(currentYear, currentMonth);
                        break;
                    case "甜品站趋势":
                        GeneratePlatformKioskSalesTrend.Write(currentYear, currentMonth);
                        break;
                    case "MTDP&L数据":
                        GenerateMtdPLData.Write(currentYear, currentMonth);
                        break;
                    case "YTDP&L数据":
                        GenerateYtdPLData.Write(currentYear, currentMonth);
                        break;
                    case "保本营业额":
                        GenerateGuaranteedRevenue.Write(currentYear, currentMonth);
                        break;
                    case "公司资产情况":
                        GenerateBalanceSheet.Write(currentYear, currentMonth);
                        break;

                }
            }
            MessageBox.Show("生成完毕!");
        }

        /// <summary>
        /// 批量导入DayPart
        /// </summary>
        private void InputForDayPart()
        {
            using (var db = new FinancialReportContext())
            {
                foreach (var file in InputFiles)
                {
                    InputExcel = new ExcelPackage(file);
                    ExcelWorksheet peaksheet = InputExcel.Workbook.Worksheets[0];
                    for (int r = peaksheet.Dimension.Start.Row + 2; r <= peaksheet.Dimension.End.Row; r++)
                    {
                        var store = peaksheet.Cells[r, 1].GetValue<string>();

                        if (string.IsNullOrWhiteSpace(store))
                        {
                            continue;
                        }
                        DateTime dateresult;
                        string strdate = peaksheet.Cells[r, 2].GetValue<string>();
                        DateTime.TryParseExact(strdate, "yyyyMMdd", null, System.Globalization.DateTimeStyles.None, out dateresult);
                        db.PeakHourSales?.Add(new PeakHourSales()
                        {
                            Uscode = store.Split(' ')[0],
                            PeakDate = dateresult,
                            Category = peaksheet.Cells[r, 19].GetValue<string>(),
                            全店Sales = peaksheet.Cells[r, 3].GetValue<decimal>(),
                            全店GC = peaksheet.Cells[r, 4].GetValue<decimal>(),
                            FCSales = peaksheet.Cells[r, 7].GetValue<decimal>(),
                            FCGC = peaksheet.Cells[r, 8].GetValue<decimal>(),
                            MDSSales = peaksheet.Cells[r, 11].GetValue<decimal>(),
                            MDSGC = peaksheet.Cells[r, 12].GetValue<decimal>(),
                            KioskSales = peaksheet.Cells[r, 15].GetValue<decimal>(),
                            KioskGC = peaksheet.Cells[r, 16].GetValue<decimal>()
                        });
                    }
                    int count = db.SaveChanges();
                    if (count > 0)
                    {
                        MessageBox.Show($"导入完成{Path.GetFileNameWithoutExtension(file)}DayPart文件，导入了{count}条数据！");
                    }
                    else
                    {
                        MessageBox.Show($"导入失败{Path.GetFileNameWithoutExtension(file)}DayPart文件！");
                    }
                }
            }
        }

        /// <summary>
        /// 批量导入系统Sales平台数据
        /// </summary>
        private void InputForPlatformSales()
        {
            using (var db = new FinancialReportContext())
            {
                foreach (var file in InputFiles)
                {
                    InputExcel = new ExcelPackage(file);
                    ExcelWorksheet platformsheet = InputExcel.Workbook.Worksheets[0];
                    for (int r = platformsheet.Dimension.Start.Row + 2; r <= platformsheet.Dimension.End.Row; r++)
                    {
                        var store = platformsheet.Cells[r, 1].GetValue<string>();
                        if (string.IsNullOrWhiteSpace(store))
                        {
                            continue;
                        }
                        db.PlatformSales?.Add(new PlatformSales()
                        {
                            Uscode = store.Split(' ')[0],
                            Year = Convert.ToInt32(Path.GetFileNameWithoutExtension(file).Split('-')[0]),
                            Month = Convert.ToInt32(Path.GetFileNameWithoutExtension(file).Split('-')[1]),
                            全店产品净收入 = platformsheet.Cells[r, 4].GetValue<decimal>(),
                            全店非品净收入 = platformsheet.Cells[r, 6].GetValue<decimal>(),
                            全店营业净收入 = platformsheet.Cells[r, 8].GetValue<decimal>(),
                            全店GC = platformsheet.Cells[r, 11].GetValue<decimal>(),
                            全店SalesComp = platformsheet.Cells[r, 10].GetValue<decimal>(),
                            FC产品净收入 = platformsheet.Cells[r, 15].GetValue<decimal>(),
                            FC非品净收入 = platformsheet.Cells[r, 17].GetValue<decimal>(),
                            FC营业净收入 = platformsheet.Cells[r, 19].GetValue<decimal>(),
                            FCGC = platformsheet.Cells[r, 22].GetValue<decimal>(),
                            FCSalesComp = platformsheet.Cells[r, 21].GetValue<decimal>(),
                            MDS产品净收入 = platformsheet.Cells[r, 26].GetValue<decimal>(),
                            MDS非品净收入 = platformsheet.Cells[r, 28].GetValue<decimal>(),
                            MDS营业净收入 = platformsheet.Cells[r, 30].GetValue<decimal>(),
                            MDSGC = platformsheet.Cells[r, 33].GetValue<decimal>(),
                            MDSSalesComp = platformsheet.Cells[r, 32].GetValue<decimal>(),
                            Kiosk产品净收入 = platformsheet.Cells[r, 37].GetValue<decimal>(),
                            Kiosk非品净收入 = platformsheet.Cells[r, 39].GetValue<decimal>(),
                            Kiosk营业净收入 = platformsheet.Cells[r, 41].GetValue<decimal>(),
                            KioskGC = platformsheet.Cells[r, 44].GetValue<decimal>(),
                            KioskSalesComp = platformsheet.Cells[r, 43].GetValue<decimal>(),
                            RemoteKiosk产品净收入 = platformsheet.Cells[r, 48].GetValue<decimal>(),
                            RemoteKiosk非品净收入 = platformsheet.Cells[r, 50].GetValue<decimal>(),
                            RemoteKiosk营业净收入 = platformsheet.Cells[r, 52].GetValue<decimal>(),
                            RemoteKioskGC = platformsheet.Cells[r, 55].GetValue<decimal>(),
                            RemoteKioskSalesComp = platformsheet.Cells[r, 54].GetValue<decimal>(),
                        });
                    }
                    int count = db.SaveChanges();
                    if (count > 0)
                    {
                        MessageBox.Show($"导入完成{Path.GetFileNameWithoutExtension(file)}平台Sales文件，导入了{count}条数据！");
                    }
                    else
                    {
                        MessageBox.Show($"导入失败{Path.GetFileNameWithoutExtension(file)}平台Sales文件！");
                    }
                }
            }
        }

        public static GenerateReportForm GetWindow()
        {   //当子窗体没有开启或者已经释放。就可以创建新窗体
            if (gr == null || gr.IsDisposed)
            {
                gr = new GenerateReportForm();
            }
            return gr;
        }

        private void btnRight_Click(object sender, EventArgs e)
        {
            listBoxRight.Items.Add(listBoxLeft.SelectedItem);
            listBoxLeft.Items.Remove(listBoxLeft.SelectedItem);
        }

        private void btnLeft_Click(object sender, EventArgs e)
        {
            listBoxLeft.Items.Add(listBoxRight.SelectedItem);
            listBoxRight.Items.Remove(listBoxRight.SelectedItem);
        }

        private void GenerateReportForm_Load(object sender, EventArgs e)
        {
            int nowYear = DateTime.Now.Year;
            int nowMonth = DateTime.Now.Month;
            for (int i = 2019; i <= nowYear; i++)
            {
                cobCompYear.Items.Add(i.ToString());
                cobCurrentYear.Items.Add(i.ToString());
            }
            cobCurrentYear.SelectedIndex = cobCurrentYear.Items.Count - 1;
            cobCompYear.SelectedIndex = cobCompYear.Items.Count - 2;
            cobCompMonth.SelectedIndex = nowMonth - 1;
            cobCurrentMonth.SelectedIndex = nowMonth - 1;
        }

        private void btnQuery_Click(object sender, EventArgs e)
        {
            int nowYear = DateTime.Now.Year;
            //展示状态信息
            using (var db = new FinancialReportContext())
            {
                int maxYear = db.PLDatas.Where(x => x.IsPlan == false).Max(x => x.Year);
                int maxMonth = db.PLDatas.Where(x => x.Year == maxYear && x.IsPlan == false).Max(x => x.Month);
                lblPLActual状态.Text = $"P&&L：Actual数据更新到{maxYear}年{maxMonth}月";
                lblPLActual状态.ForeColor = Color.Red;

                maxYear = db.PLDatas.Where(x => x.IsPlan == true).Max(x => x.Year);
                maxMonth = db.PLDatas.Where(x => x.Year == maxYear && x.IsPlan == true).Max(x => x.Month);
                lblPLPlan状态.Text = $"P&&L：Plan数据更新到{maxYear}年{maxMonth}月";
                lblPLPlan状态.ForeColor = Color.Red;

                maxYear = db.PlatformSales.Max(x => x.Year);
                maxMonth = db.PlatformSales.Where(x => x.Year == maxYear).Max(x => x.Month);
                lblPlatformSales状态.Text = $"Sales：Platform数据更新到{maxYear}年{maxMonth}月";
                lblPlatformSales状态.ForeColor = Color.Red;

                maxYear = db.PeakHourSales.Max(x => x.PeakDate.Year);
                maxMonth = db.PeakHourSales.Where(x => x.PeakDate.Year == maxYear).Max(x => x.PeakDate.Month);
                lblPeakHour状态.Text = $"Sales：PeakHour数据更新到{maxYear}年{maxMonth}月";
                lblPeakHour状态.ForeColor = Color.Red;

                maxYear = db.Fobs.Max(x => x.Year);
                maxMonth = db.Fobs.Where(x => x.Year == maxYear).Max(x => x.Month);
                lblFOB状态.Text = $"FOB：数据更新到{maxYear}年{maxMonth}月";
                lblFOB状态.ForeColor = Color.Red;

                maxYear = db.MdsQuantities.Max(x => x.Year);
                maxMonth = db.MdsQuantities.Where(x => x.Year == maxYear).Max(x => x.Month);
                lblMds状态.Text = $"MDS：单量数据更新到{maxYear}年{maxMonth}月";
                lblMds状态.ForeColor = Color.Red;

                maxYear = db.ManageNumbers.Max(x => x.Year);
                maxMonth = db.ManageNumbers.Where(x => x.Year == maxYear).Max(x => x.Month);
                lblManage状态.Text = $"管理组人数：数据更新到{maxYear}年{maxMonth}月";
                lblManage状态.ForeColor = Color.Red;

                maxYear = db.CrewLabors.Max(x => x.Year);
                maxMonth = db.CrewLabors.Where(x => x.Year == maxYear).Max(x => x.Month);
                lblCrewLabor状态.Text = $"Pthr：数据更新到{maxYear}年{maxMonth}月";
                lblCrewLabor状态.ForeColor = Color.Red;

                maxYear = db.ProfitSheets.Max(x => x.Year);
                maxMonth = db.ProfitSheets.Where(x => x.Year == maxYear).Max(x => x.Month);
                lblProfitSheet状态.Text = $"利润表：数据更新到{maxYear}年{maxMonth}月";
                lblProfitSheet状态.ForeColor = Color.Red;

                maxYear = db.BalanceSheets.Max(x => x.Year);
                maxMonth = db.BalanceSheets.Where(x => x.Year == maxYear).Max(x => x.Month);
                lblBalanceSheet状态.Text = $"资产负债表：数据更新到{maxYear}年{maxMonth}月";
                lblBalanceSheet状态.ForeColor = Color.Red;
            }
        }

        /// <summary>
        /// 加载PL
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btn加载_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            //打开的文件选择对话框上的标题
            openFileDialog.Title = "请选择文件";
            //设置文件类型
            openFileDialog.Filter = "Excel文件(*.xls)|*.xlsx|全部文件(*.*)|*.*";
            //设置默认文件类型显示顺序
            openFileDialog.FilterIndex = 1;
            //保存对话框是否记忆上次打开的文件夹
            openFileDialog.RestoreDirectory = true;
            //设置是否同意多选
            openFileDialog.Multiselect = true;
            //按下确定选择的button
            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                InputFiles = openFileDialog.FileNames;
                this.lab加载.Text = "就绪";
                this.lab加载.ForeColor = Color.Red;
            }
        }

        /// <summary>
        /// 导入人员数量
        /// </summary>
        private void InputManageNumber()
        {
            using (var db = new FinancialReportContext())
            {
                foreach (var file in InputFiles)
                {
                    InputExcel = new ExcelPackage(file);
                    ExcelWorksheet peopleNumberSheet = InputExcel.Workbook.Worksheets[0];
                    for (int r = peopleNumberSheet.Dimension.Start.Row + 1; r <= peopleNumberSheet.Dimension.End.Row; r++)
                    {
                        var code = peopleNumberSheet.Cells[r, 1].GetValue<string>();
                        if (string.IsNullOrWhiteSpace(code))
                        {
                            continue;
                        }
                        db.ManageNumbers?.Add(new ManageNumber()
                        {
                            Uscode = code,
                            Year = Convert.ToInt32(peopleNumberSheet.Cells[r, 6].GetValue<string>().Split('-')[0]),
                            Month = Convert.ToInt32(peopleNumberSheet.Cells[r, 6].GetValue<string>().Split('-')[1]),
                            RGM = peopleNumberSheet.Cells[r, 5].GetValue<int>(),
                            RPM = peopleNumberSheet.Cells[r, 4].GetValue<int>(),
                            RDM = peopleNumberSheet.Cells[r, 3].GetValue<int>(),
                            GEL = peopleNumberSheet.Cells[r, 2].GetValue<int>(),
                        });
                    }
                    int count = db.SaveChanges();
                    if (count > 0)
                    {
                        MessageBox.Show($"导入完成{Path.GetFileNameWithoutExtension(file)}文件，导入了{count}条数据！");
                    }
                    else
                    {
                        MessageBox.Show($"导入失败{Path.GetFileNameWithoutExtension(file)}文件！");
                    }
                }
            }
        }

        /// <summary>
        /// 导入员工工时
        /// </summary>
        private void InputCrewLabor()
        {
            using (var db = new FinancialReportContext())
            {
                foreach (var file in InputFiles)
                {
                    InputExcel = new ExcelPackage(file);
                    ExcelWorksheet crewLaborSheet = InputExcel.Workbook.Worksheets[0];
                    int deleteCount = 0;
                    for (int r = crewLaborSheet.Dimension.Start.Row + 1; r <= crewLaborSheet.Dimension.End.Row; r++)
                    {
                        var code = crewLaborSheet.Cells[r, 1].GetValue<string>();
                        if (string.IsNullOrWhiteSpace(code))
                        {
                            continue;
                        }
                        int year = Convert.ToInt32(crewLaborSheet.Cells[r, 9].GetValue<string>().Split('-')[0]);
                        int month = Convert.ToInt32(crewLaborSheet.Cells[r, 9].GetValue<string>().Split('-')[1]);
                        //先判断是否存在记录
                        var exist = db.CrewLabors.FirstOrDefault(x => x.Uscode == code && x.Month == month && x.Year == year);
                        if (exist != null)
                        {
                            db.CrewLabors.Remove(exist);
                            deleteCount++;
                        }
                        db.CrewLabors?.Add(new CrewLabor()
                        {
                            Uscode = code,
                            Year = year,
                            Month = month,
                            非全 = crewLaborSheet.Cells[r, 2].GetValue<decimal>(),
                            兼职人员 = crewLaborSheet.Cells[r, 3].GetValue<decimal>(),
                            全职 = crewLaborSheet.Cells[r, 4].GetValue<decimal>(),
                            实习生 = crewLaborSheet.Cells[r, 5].GetValue<decimal>(),
                            退休 = crewLaborSheet.Cells[r, 6].GetValue<decimal>(),
                            学生 = crewLaborSheet.Cells[r, 7].GetValue<decimal>()
                        });
                    }
                    int count = db.SaveChanges();
                    if (count > 0)
                    {
                        string str = string.Empty;
                        if (deleteCount > 0)
                        {
                            str = "，删除了" + deleteCount.ToString() + "条记录，";
                        }
                        MessageBox.Show($"导入完成{Path.GetFileNameWithoutExtension(file)}文件，{str}导入了{count - deleteCount}条数据！");
                    }
                    else
                    {
                        MessageBox.Show($"导入失败{Path.GetFileNameWithoutExtension(file)}文件！");
                    }
                }
            }
        }

        /// <summary>
        /// 导入MDS单量
        /// </summary>
        private void InputMDSQuantity()
        {
            using (var db = new FinancialReportContext())
            {
                foreach (var file in InputFiles)
                {
                    InputExcel = new ExcelPackage(file);
                    ExcelWorksheet mdsQuantitySheet = InputExcel.Workbook.Worksheets[0];
                    for (int r = mdsQuantitySheet.Dimension.Start.Row + 1; r <= mdsQuantitySheet.Dimension.End.Row; r++)
                    {
                        var code = mdsQuantitySheet.Cells[r, 1].GetValue<string>();
                        if (string.IsNullOrWhiteSpace(code))
                        {
                            continue;
                        }
                        db.MdsQuantities?.Add(new MdsQuantity()
                        {
                            Uscode = code,
                            Year = Convert.ToInt32(mdsQuantitySheet.Cells[r, 3].GetValue<string>().Split('-')[0]),
                            Month = Convert.ToInt32(mdsQuantitySheet.Cells[r, 3].GetValue<string>().Split('-')[1]),
                            Quantity = mdsQuantitySheet.Cells[r, 2].GetValue<int>()
                        });
                    }
                    int count = db.SaveChanges();
                    if (count > 0)
                    {
                        MessageBox.Show($"导入完成{Path.GetFileNameWithoutExtension(file)}文件，导入了{count}条数据！");
                    }
                    else
                    {
                        MessageBox.Show($"导入失败{Path.GetFileNameWithoutExtension(file)}文件！");
                    }
                }
            }
        }
    }
}
